package com.zis.common.excel.io;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zis.common.excel.db.DBRecord;
import com.zis.common.excel.db.DBRecordSet;
import com.zis.common.excel.db.DBSheet;
import com.zis.common.excel.metadata.ColumnMetadata;
import com.zis.common.excel.metadata.ReportMetadata;
import com.zis.common.excel.metadata.SheetMetadata;
import com.zis.util.FileUtil;

/**
 * 
 * <b>说明：</b> excel导出：写excel文件
 * 
 * @ClassName: ExcelWriter
 * @author zhaohaitao(2543) 日期： 2015-7-24 上午11:24:39
 * 
 */
public class ExcelWriter
{
    /**
     * excel模板路径
     */
    public String templatePath;
    
    /**
     * 保存到的目标位置
     */
    public String savePath;
    
    public ExcelWriter()
    {
    }
    
    public boolean write(List list_dbsheet, ReportMetadata reportMetadata)
    {
        boolean result = false;
        try
        {
            if (templatePath == null || templatePath.trim().equals(""))
            {
                throw new Exception("templatePath 不能为空。");
            }
            
            if (savePath == null || savePath.trim().equals(""))
            {
                throw new Exception("保存路径 不能为空。");
            }
            if (savePath.endsWith("\\"))
            {
                File saveFile = new File(savePath);
                if (!saveFile.exists())
                {
                    saveFile.mkdirs();
                }
                String templateFilePath =
                    (new StringBuilder(String.valueOf(templatePath))).append(reportMetadata.getTemplateFileName())
                        .toString();
                String tartgetPath =
                    (new StringBuilder(String.valueOf(savePath))).append(reportMetadata.getReportName()).toString();
                FileUtil.copy(templateFilePath, tartgetPath);
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(tartgetPath));
                HSSFCellStyle cs = wb.createCellStyle();
                cs.setWrapText(true);
                cs.setAlignment((short)2);
                Map sheetMetadatas = reportMetadata.getSheetMetadatas();
                for (int i = 0; i < list_dbsheet.size(); i++)
                {
                    DBSheet sheetdb = (DBSheet)list_dbsheet.get(i);
                    HSSFSheet sheet = wb.getSheet(sheetdb.getSheetName());
                    SheetMetadata sheetMetadata = (SheetMetadata)sheetMetadatas.get(sheetdb.getSheetName());
                    DBRecordSet dBRecordSet = sheetdb.getRecordSet();
                    String direction = sheetMetadata.getDirection();
                    if (direction.equals("0"))
                    {
                        createXSheet(sheet, sheetMetadata, dBRecordSet, cs);
                    }
                    else if (direction.equals("1"))
                    {
                        createYSheet(sheet, sheetMetadata, dBRecordSet, cs);
                    }
                    else if (direction.equals("2"))
                    {
                        createXYSheet(sheet, sheetMetadata, dBRecordSet, cs);
                    }
                    wb.setSheetName(i, sheetdb.getDisplayName());
                }
                
                OutputStream fileOut = new FileOutputStream(tartgetPath);
                wb.write(fileOut);
                fileOut.close();
                result = true;
            }
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return result;
    }
    
    private void createXSheet(HSSFSheet sheet, SheetMetadata sheetMetadata, DBRecordSet dBRecordSet, HSSFCellStyle style)
    {
        for (int i = 0; i < dBRecordSet.size(); i++)
        {
            HSSFRow row = getRow(sheet, i + 1);
            DBRecord dbRecord = dBRecordSet.getRow(i);
            Map columnMap = sheetMetadata.getColumnMetadatas();
            for (Iterator rt = columnMap.keySet().iterator(); rt.hasNext();)
            {
                String dbColumn = (String)rt.next();
                ColumnMetadata columnMetadata = (ColumnMetadata)columnMap.get(dbColumn);
                String excelColumnStr = columnMetadata.getExcelColumn();
                int columnNum = Integer.parseInt(excelColumnStr);
                String value = dbRecord.getString(dbColumn);
                HSSFCell cell = getCell(row, columnNum);
                cell.setCellStyle(style);
                setValueToExcelCell(cell, value, columnMetadata.getDataType());
                String line = sheetMetadata.getPartitionLine();
                if (line != null && line.equals("1"))
                {
                    sheet.createRow(i + 2);
                }
            }
        }
    }
    
    private void createYSheet(HSSFSheet sheet, SheetMetadata sheetMetadata, DBRecordSet dBRecordSet, HSSFCellStyle style)
    {
        for (int i = 0; i < dBRecordSet.size(); i++)
        {
            DBRecord dbRecord = dBRecordSet.getRow(i);
            Map columnMap = sheetMetadata.getColumnMetadatas();
            ColumnMetadata columnMetadata;
            String value;
            HSSFCell cell;
            for (Iterator rt = columnMap.keySet().iterator(); rt.hasNext(); setValueToExcelCell(cell,
                value,
                columnMetadata.getDataType()))
            {
                String dbColumn = (String)rt.next();
                columnMetadata = (ColumnMetadata)columnMap.get(dbColumn);
                String excelColumnStr = columnMetadata.getExcelColumn();
                int rowNum = Integer.parseInt(excelColumnStr);
                HSSFRow row = getRow(sheet, rowNum);
                value = dbRecord.getString(dbColumn);
                cell = getCell(row, i + 1);
                cell.setCellStyle(style);
            }
            
        }
        
    }
    
    private void createXYSheet(HSSFSheet sheet, SheetMetadata sheetMetadata, DBRecordSet dBRecordSet,
        HSSFCellStyle style)
    {
        Hashtable tableX = new Hashtable();
        Hashtable tableY = new Hashtable();
        String columnX = sheetMetadata.getColumnX();
        String columnY = sheetMetadata.getColumnY();
        String dataColumn = sheetMetadata.getDataColumn();
        int startIndex = Integer.parseInt(sheetMetadata.getStartIndex());
        int x = startIndex;
        int y = 1;
        for (int j = 0; j < dBRecordSet.size(); j++)
        {
            DBRecord dbRecord = dBRecordSet.getRow(j);
            String valueX = dbRecord.getString(columnX);
            String valueY = dbRecord.getString(columnY);
            if (tableX.get(valueX) == null)
            {
                tableX.put(valueX, (new StringBuilder(String.valueOf(x))).toString());
                HSSFRow row = sheet.getRow(0);
                HSSFCell cell = row.getCell(x);
                cell.setCellValue(valueX);
                x++;
            }
            if (tableY.get(valueY) == null)
            {
                tableY.put(valueY, (new StringBuilder(String.valueOf(y))).toString());
                HSSFRow row = sheet.getRow(y);
                HSSFCell cell = row.getCell(0);
                cell.setCellValue(valueY);
                String line = sheetMetadata.getPartitionLine();
                if (line != null && line.equals("1"))
                {
                    y++;
                    sheet.getRow((short)y);
                }
                y++;
            }
        }
        
        for (int j = 0; j < dBRecordSet.size(); j++)
        {
            DBRecord dbRecord = dBRecordSet.getRow(j);
            String value = dbRecord.getString(dataColumn);
            String valueX = dbRecord.getString(columnX);
            String valueY = dbRecord.getString(columnY);
            int rowIndex = Integer.parseInt(tableY.get(valueY).toString());
            int cellIndex = Integer.parseInt(tableX.get(valueX).toString());
            HSSFRow row = sheet.getRow(rowIndex);
            HSSFCell cell = row.getCell(cellIndex);
            cell.setCellStyle(style);
            cell.setCellValue(value);
        }
        
    }
    
    private HSSFRow getRow(HSSFSheet sheet, int rowIndex)
    {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null)
        {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }
    
    private HSSFCell getCell(HSSFRow row, int columnNum)
    {
        HSSFCell cell = row.getCell(columnNum);
        if (cell == null)
        {
            cell = row.createCell((short)columnNum);
        }
        return cell;
    }
    
    private void setValueToExcelCell(HSSFCell cell, String value, String dataType)
    {
        if (dataType != null)
        {
            dataType = dataType.trim();
            if ("String".equals(dataType))
            {
                cell.setCellValue(value);
            }
            else if ("int".equals(dataType))
            {
                int intValue = Integer.parseInt(value);
                cell.setCellValue(intValue);
            }
            else if ("long".equals(dataType))
            {
                long longValue = Long.parseLong(value);
                cell.setCellValue(longValue);
            }
            else if ("double".equals(dataType))
            {
                double doubleValue = Double.parseDouble(value);
                cell.setCellValue(doubleValue);
            }
        }
    }
    
    public String getTemplatePath()
    {
        return templatePath;
    }
    
    public void setTemplatePath(String templatePath)
    {
        this.templatePath = templatePath;
    }
    
    public String getSavePath()
    {
        return savePath;
    }
    
    public void setSavePath(String savePath)
    {
        this.savePath = savePath;
    }
    
}
